package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.ProductDao;
import com.cn.wanxi.model.ProductModel;
import com.cn.wanxi.service.ProductService;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductImpl implements ProductDao {
    @Override
    public List<ProductModel> findAll(ProductModel model) {
        String sql = "SELECT p.*,c.name cuisineName FROM product p\n" +
                "left join cuisine c\n";
        sql += "on p.cuisine_id=c.id  ";
        if (model.getCuisineId() != -1) {
            sql += " and cuisine_id=" + model.getCuisineId();
        }
        sql += " order by id desc";
        sql += " limit " + (model.getPageNo() - 1) * model.getPageSize() + ", " + model.getPageSize() + "";
        ResultSet resultSet = JDBC.query(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ProductModel productModel = new ProductModel();
                productModel.setId(resultSet.getInt("id"));
                productModel.setName(resultSet.getString("name"));
                productModel.setNormalPrice(resultSet.getBigDecimal("normal_price"));
                productModel.setMarketPrice(resultSet.getBigDecimal("price"));
                productModel.setImgHref(resultSet.getString("img_href"));
                productModel.setIsShow(resultSet.getInt("is_show"));
                productModel.setIsRecommend(resultSet.getInt("is_recommend"));
                productModel.setCuisineName(resultSet.getString("cuisineName"));
                list.add(productModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int add(ProductModel model) {
        String sql = "INSERT INTO `product` (`name`, " +
                "`normal_price`, `price`, " +
                "`content`, `is_show`, `is_recommend`, " +
                "`img_href`, `create_time`, `update_time`," +
                " `remark`, `enable`, " +
                "`where_show`, `cuisine_id`) VALUES (?, " +
                "?,?, " +
                "?, ?, ?," +
                " ?,now(), now()," +
                " '', ?," +
                " '1', ?);\n";
        List list = new ArrayList();
        list.add(model.getName());
        list.add(model.getNormalPrice());
        list.add(model.getMarketPrice());
        list.add(model.getContent());
        list.add(model.getIsShow());
        list.add(model.getIsRecommend());
        list.add(model.getImgHref());
        list.add(model.getEnable());
        list.add(model.getCuisineId());
        return JDBC.updatea(sql, list);
    }

    @Override
    public int update(ProductModel model) {
        return 0;
    }

    @Override
    public ProductModel findById(ProductModel model) {
        return null;
    }

    @Override
    public int delete(ProductModel model) {
        return 0;
    }

    @Override
    public int getCount(ProductModel productModel) {
        String sql = "SELECT count(*) count  FROM product p\n" +
                "inner join cuisine c\n" +
                "on p.cuisine_id=c.id  ";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<ProductModel> getProductListForHomePage() {
        String sql = "SELECT p.*,c.name cuisineName FROM product p\n" +
                "left join cuisine c\n";
        sql += " on  p.where_show=1 " +
                " and p.enable=1 ";
        sql += " and p.cuisine_id=c.id  order by p.create_time desc ";
        sql += " limit 2";
        ResultSet resultSet = JDBC.query(sql);
        List<ProductModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                ProductModel productModel = new ProductModel();
                productModel.setId(resultSet.getInt("id"));
                productModel.setName(resultSet.getString("name"));
                productModel.setNormalPrice(resultSet.getBigDecimal("normal_price"));
                productModel.setMarketPrice(resultSet.getBigDecimal("price"));
                productModel.setImgHref(resultSet.getString("img_href"));
                productModel.setIsShow(resultSet.getInt("is_show"));
                productModel.setIsRecommend(resultSet.getInt("is_recommend"));
                productModel.setCuisineName(resultSet.getString("cuisineName"));
                productModel.setContent(resultSet.getString("content"));
                list.add(productModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 1.查询两条热点+两条置顶+四条普通
     * 2.如果没有热点或者热点少于两条 +两条置顶+5条普通
     * 3.如果没有热点或者置顶，则直接查询八条普通数据
     *  -------------------------
     *  能够在sql语句里面做就不要在Java里面做
     *  能够在js里面做就不要在Java里面做
     *
     * @param model
     * @return
     */
    @Override
    public List<ProductModel> findAllFront(ProductModel model) {
        //查询热点产品
        String sqlHot = "SELECT p.*,c.name cuisineName FROM product p,cuisine c  where p.is_show=1  ";
        if(model.getCuisineId()!=-1){
            sqlHot+=" and p.cuisine_id=" + model.getCuisineId() ;
        }


        sqlHot += " limit " + (model.getPageNo() - 1) * 2 + "," + 2;
        List<ProductModel> listAll = new ArrayList<>();
        List<ProductModel> listHot = getProductList(sqlHot, model);
        //将热点产品放入要返回的list
        for (int i = 0; i < listHot.size(); i++) {
            listAll.add(listHot.get(i));
        }
        //查询置顶的产品
        String sqlTop = "SELECT  p.*,c.name cuisineName FROM product p,cuisine c  where p.is_recommend=1 ";
        if(model.getCuisineId()!=-1){
            sqlTop+=" and p.cuisine_id=" + model.getCuisineId() ;
        }
        sqlTop += " limit " + (model.getPageNo() - 1) * 2 + "," + 2;
        List<ProductModel> listTop = getProductList(sqlTop, model);
        //将置顶产品放入要返回的list
        for (int i = 0; i < listTop.size(); i++) {
            listAll.add(listTop.get(i));
        }
        //查询普通的产品 ，总条数应该为8条减去热点和新闻的条数
        String sqlNormal = "SELECT  p.*,c.name cuisineName FROM product p,cuisine c  where p.is_recommend!=1 or p.is_show!=1  ";
        if(model.getCuisineId()!=-1){
            sqlNormal+=" and p.cuisine_id=" + model.getCuisineId() ;
        }
        Integer pageSize=model.getPageSize()-listAll.size();
        sqlNormal += " limit " + (model.getPageNo() - 1) * pageSize + "," + pageSize;
        List<ProductModel> listNormal = getProductList(sqlNormal, model);
        //将普通的放入list
        for (int i = 0; i < listNormal.size(); i++) {
            listAll.add(listNormal.get(i));
        }
        return listAll;
    }

    @Override
    public int getCountFront(ProductModel productModel) {
        return 0;
    }

    @Override
    public List<ProductModel> findByIdFront(ProductModel productModel) {
        String sql="(SELECT p.*,c.name cuisineName FROM product p,cuisine c   where p.cuisine_id=c.id  and p.id=(select min(id) from product where id>"+productModel.getId()+"))\n" +
                "union all\n" +
                "(SELECT p.*,c.name cuisineName FROM product p,cuisine c   where p.cuisine_id=c.id  and  p.id="+productModel.getId()+")\n" +
                "union all\n" +
                "(SELECT p.*,c.name cuisineName FROM product p,cuisine c   where p.cuisine_id=c.id  and p.id=(select max(id) from product where id<"+productModel.getId()+"))";

        return getProductList(sql,productModel);
    }

    /**
     * 将所有的sql语句封装然后查询返回所需要的数据
     * @param sql
     * @param model
     * @return
     */
    private List<ProductModel> getProductList(String sql, ProductModel model) {
        List<ProductModel> list = new ArrayList<>();
//        if(model.getName()!=""){
//            sql+=" and name like '%"+model.getName()+"%'";
//        }
//        sql+="limit 1,10";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                ProductModel productModel = new ProductModel();
                productModel.setId(resultSet.getInt("id"));
                productModel.setName(resultSet.getString("name"));
                productModel.setNormalPrice(resultSet.getBigDecimal("normal_price"));
                productModel.setMarketPrice(resultSet.getBigDecimal("price"));
                productModel.setImgHref(resultSet.getString("img_href"));
                productModel.setIsShow(resultSet.getInt("is_show"));
                productModel.setIsRecommend(resultSet.getInt("is_recommend"));
                productModel.setCuisineName(resultSet.getString("cuisineName"));
                productModel.setContent(resultSet.getString("content"));
                list.add(productModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}
